/*
 * TransactionDAO.java
 *
 * Created on February 28, 2007, 8:58 AM
 *
 */

package edu.byu.isys413.group1E.data;

import edu.byu.isys413.group1E.*;
import java.sql.*;
import java.util.ArrayList;

/** Reads and writes TransactionBO data to and from the database.
 *
 * @author Travis Kulbeth
 */
public class TransactionDAO {
    
    /** Contains an instance of TransactionDAO*/
    private static TransactionDAO instance = null;
    
    /** Creates a new instance of TransactionDAO */
    public TransactionDAO() {
    }
    
    /**Retrieves an instance of the TransactionDAO object
     *
     * @return a current singleton instance of the TransactionDAO class
     */
    public static synchronized TransactionDAO getInstance() {
        if (instance == null) {
            instance = new TransactionDAO();
        }
        return instance;
    }
    
    //////////////////////////////////////////////////////////////////////////////
    //////////   CREATE
    /**
     * Creates a new TransactionBO object with a newly generated GUID.
     * 
     * @return a newly created TransactionBO with unique GUID
     * @throws java.lang.Exception to catch any errors in the creation process.
     */
    public TransactionBO create() throws Exception{
        String id = GUID.generate();
        TransactionBO bo = new TransactionBO(id);
        Cache c = Cache.getInstance();
        c.put(bo.getTransID(), bo);
        return bo;
    }
    
    //////////////////////////////////////////////////////////////////////////////
    //////////   READ
    /**
     * This is the public read statement.  It loads an existing Transaction record
     * from the database based on the Transaction id and returns a TransactionBO.
     * 
     * @return a TransactionBO containing the information extracted from the requested
     * tuple in the DB.
     * @param id contains the unique identifier for the record to be read from the DB.
     * @throws edu.byu.isys413.group1E.DataException to catch any errors with the incoming data
     * @throws edu.byu.isys413.group1E.ConnectionPoolException to catch errors when connecting to the DB
     */
    public synchronized TransactionBO read(String id) throws DataException, ConnectionPoolException {
        // check to see if id in the cache
        Cache c = Cache.getInstance();
        TransactionBO trans = (TransactionBO)c.get(id);
        if (trans != null){
            return trans;                                                        // if so, return it immediately
        }
        
        //otherwise, get it from the database
        try {
            Connection conn = ConnectionPool.getInstance().get();             // retrieve a database connection from the pool
            trans = this.read(id, conn);                                              // call read with a connection
            conn.commit();
            ConnectionPool.getInstance().release(conn);                       // release the connection
        }catch (SQLException e) {
            throw new DataException("Could not retrieve record for Transaction ID = " + id, e);
        }catch (ConnectionPoolException x) {
            throw new ConnectionPoolException("Could not establish connection to database.", x);
        }
        
        // release the connection back to the pool
        return trans;
    }
    
    /**
     * This is the public read statement.  It loads an existing Transaction record
     * from the database based on the Transaction id, using an already existing connection to the DB,
     * and returns a TransactionBO.
     * 
     * @return TransactionBO
     * @param id contains the given id for the Transaction to be read
     * @param conn contains an already open connection to the DB
     * @throws edu.byu.isys413.group1E.DataException catches any errors with incoming data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     * @throws edu.byu.isys413.group1E.ConnectionPoolException catches any errors when connecting to the DB.
     */
    synchronized TransactionBO read(String id, Connection conn) throws SQLException, DataException, ConnectionPoolException {
        // check to see if id in the cache
        Cache c = Cache.getInstance();
        TransactionBO trans = (TransactionBO)c.get(id);
        if (trans != null){
            return trans;                                                        // if so, return it immediately
        }
        
        // if not in the cache, get a result set from
        // a SELECT * FROM table WHERE id=guid
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM Transaction_tbl WHERE TransactionID = '" + id + "'");
        conn.commit();
        
        //build the object from the result set
        if (rs.next()) {
            trans = new TransactionBO(rs.getString("TransactionID"));
            trans.setTransDate(rs.getDate("TxDate").toString());
            trans.setTransTaxRate(rs.getDouble("TaxRate"));
            trans.setTransTax(rs.getDouble("TotalTax"));
            trans.setTransSubTotal(rs.getDouble("SubTotal"));
            trans.setTransTotal(rs.getDouble("Total"));
            trans.setTotalDiscountTaken(rs.getDouble("DiscountTaken"));
            trans.setTransStatus(rs.getString("Status"));
            trans.setEmployeeID(rs.getString("EmployeeID"));
            trans.setStoreID(rs.getString("StoreID"));
            //set the customer id if customer was not anonymous
            if(rs.getString("CustomerID") != null){
                trans.setCustomerID(CustomerDAO.getInstance().read(rs.getString("CustomerID")).getCustID());
            }else{
                String cust = null;
                trans.setCustomerID(cust);
            }
            
            //set the payment if one was created
            if(rs.getString("PaymentID") == null){
                PaymentBO pmt = null;
                trans.setPmt(pmt);
            }else{
                PaymentBO pmt = PaymentDAO.getInstance().read(rs.getString("PaymentID"), conn);
                trans.setPmt(pmt);
            }
            trans.setAlreadyInDB(true);
            trans.setIsDirty(false);
            
            // save to the cache
            c.put(trans.getTransID(), trans);
        }else{
            throw new DataException("Object was not found in the database.");
        }
        stmt.close();
        // return the BO
        return trans;
    }
    
    /**Search for a transaction by any combination of id, date, tax rate, status, 
     * customer, store, or employee
     * 
     * @param transID contains the requested transactionID
     * @param txDate contains the requested transaction date
     * @param taxRate contains the requested taxRate
     * @param status contains the requested transaction status
     * @param customerID contains the customerID for the requested transaction
     * @param storeID contains the requested storeID
     * @param employeeID contains the employeeID of the employee who created the Transaction
     * @throws edu.byu.isys413.group1E.DataException catches any errors with incoming data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     * @throws edu.byu.isys413.group1E.ConnectionPoolException catches any errors when connecting to the DB.
     * @return a list of TransactionBO objects that meet the above search criteria
     */
    public synchronized ArrayList<TransactionBO> searchTrans(String transID, String txDate, double taxRate, String status,
            String customerID, String storeID, String employeeID) throws
            DataException, SQLException, ConnectionPoolException{
        
        ArrayList<TransactionBO> transList = new ArrayList<TransactionBO>();
        ArrayList<String> components = new ArrayList<String>();             //create a list to store query components
        
        boolean componentAbove = false;
        //determine what parameters are searchable
        if(transID != null){
            components.add(" TransactionID = '" + transID + "'");
            componentAbove = true; }                                              //indicates that AND is needed to separate the following component
        if(txDate != null){
            if(componentAbove == true) components.add(" AND");                    //add AND if needed
            components.add(" txDate = '" + java.sql.Date.valueOf(txDate) + "'");
            componentAbove = true;}
        if(taxRate != -1){
            if(componentAbove == true) components.add(" AND");                    //add AND if needed
            components.add(" taxRate = " + taxRate);
            componentAbove = true; }
        if(status != null){
            if(componentAbove == true) components.add(" AND");                    //add AND if needed
            components.add(" Status = '" + status + "'");
            componentAbove = true; }
        if(customerID != null){
            if(componentAbove == true) components.add(" AND");                    //add AND if needed
            components.add(" CustomerID = '" + customerID + "'");
            componentAbove = true;}
        if(storeID != null){
            if(componentAbove == true) components.add(" AND");                    //add AND if needed
            components.add(" StoreID = '" + storeID + "'");
            componentAbove = true;}
        if(employeeID != null){
            if(componentAbove == true) components.add(" AND");                    //add AND if needed
            components.add(" EmployeeID = '" + employeeID + "'");
        }
        
        //now build the query
        String query = "SELECT * FROM Transaction_tbl WHERE";
        for(int i=0; i<components.size(); i++){
            query = query.concat(components.get(i));
        }
        
        Connection conn = ConnectionPool.getInstance().get();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        conn.commit();
        
        //extract the elements from the result set and build a list of results
        while(rs.next()){
            TransactionBO newTrans = new TransactionBO(rs.getString("TransactionID"));
            newTrans.setTransDate(rs.getDate("TxDate").toString());
            newTrans.setTransTaxRate(rs.getDouble("TaxRate"));
            newTrans.setTransTax(rs.getDouble("TotalTax"));
            newTrans.setTransSubTotal(rs.getDouble("SubTotal"));
            newTrans.setTransTotal(rs.getDouble("Total"));
            newTrans.setTotalDiscountTaken(rs.getDouble("DiscountTaken"));
            newTrans.setTransStatus(rs.getString("Status"));
            newTrans.setEmployeeID(rs.getString("EmployeeID"));
            newTrans.setStoreID(rs.getString("StoreID"));
            //set the customer id if customer was not anonymous
            if(rs.getString("CustomerID") != null){
                newTrans.setCustomerID(CustomerDAO.getInstance().read(rs.getString("CustomerID")).getCustID());
            }else{
                String cust = null;
                newTrans.setCustomerID(cust);
            }
            //set the payment if one was created
            if(rs.getString("PaymentID") == null){
                PaymentBO pmt = null;
                newTrans.setPmt(pmt);
            }else{
                PaymentBO pmt = PaymentDAO.getInstance().read(rs.getString("PaymentID"), conn);
                newTrans.setPmt(pmt);
            }
            newTrans.setAlreadyInDB(true);
            newTrans.setIsDirty(false);
            
            //save to the list of transactions resulting from the search
            transList.add(newTrans);
        }
        
        stmt.close();
        ConnectionPool.getInstance().release(conn);
        return transList;
    }
    
    /**This is a public method to retrieve a list of all Transactions in the DB.
     * 
     * @throws edu.byu.isys413.group1E.DataException catches any errors with incoming data
     * @throws java.sql.SQLException catches any errors with SQL execution
     * @throws edu.byu.isys413.group1E.ConnectionPoolException catches any errors when connecting to the DB.
     * @return a list of all Transactions from the DB.
     */
    public synchronized ArrayList<TransactionBO> readAllTrans() throws DataException, SQLException, ConnectionPoolException{
        ArrayList<TransactionBO> transList = new ArrayList<TransactionBO>();
        //now build the query
        String query = "SELECT * FROM Transaction_tbl";
        
        Connection conn = ConnectionPool.getInstance().get();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        conn.commit();
        
        //extract the elements from the result set and build a list of results
        while(rs.next()){
            TransactionBO newTrans = new TransactionBO(rs.getString("TransactionID"));
            newTrans.setTransDate(rs.getDate("TxDate").toString());
            newTrans.setTransTaxRate(rs.getDouble("TaxRate"));
            newTrans.setTransTax(rs.getDouble("TotalTax"));
            newTrans.setTransSubTotal(rs.getDouble("SubTotal"));
            newTrans.setTransTotal(rs.getDouble("Total"));
            newTrans.setTotalDiscountTaken(rs.getDouble("DiscountTaken"));
            newTrans.setTransStatus(rs.getString("Status"));
            newTrans.setEmployeeID(rs.getString("EmployeeID"));
            newTrans.setStoreID(rs.getString("StoreID"));
            //set the customer id if customer was not anonymous
            if(rs.getString("CustomerID") != null){
                newTrans.setCustomerID(CustomerDAO.getInstance().read(rs.getString("CustomerID")).getCustID());
            }else{
                String cust = null;
                newTrans.setCustomerID(cust);
            }
            //set the payment if one was created
            if(rs.getString("PaymentID") == null){
                PaymentBO pmt = null;
                newTrans.setPmt(pmt);
            }else{
                PaymentBO pmt = PaymentDAO.getInstance().read(rs.getString("PaymentID"), conn);
                newTrans.setPmt(pmt);
            }
            newTrans.setAlreadyInDB(true);
            newTrans.setIsDirty(false);
            
            //save to the list of transactions resulting from the search
            transList.add(newTrans);
        }
        
        stmt.close();
        ConnectionPool.getInstance().release(conn);
        return transList;
    }
    
    //////////////////////////////////
    ///   UPDATE
    
    /** This is the public save method.  It is what is called when
     * the user (controller) wants to save or update an object
     * into the database.
     * 
     * @param transIn is a TransactionBO object to be saved
     *
     * @throws edu.byu.isys413.group1E.DataException catches any errors with incoming data
     * @throws edu.byu.isys413.group1E.ConnectionPoolException catches any errors when connecting to the DB.
     */
    public synchronized void save(TransactionBO transIn) throws DataException, ConnectionPoolException {
        
        try {
            Connection conn = ConnectionPool.getInstance().get();
            try {
                this.save(transIn, conn);                                   // call save with a connection
                conn.commit();
            } catch (DataException ex) {
                conn.rollback();
                ex.printStackTrace();
            } catch (ConnectionPoolException ex) {
                conn.rollback();
                ex.printStackTrace();
            } catch (SQLException ex) {
                conn.rollback();
                throw new SQLException();
            }
            ConnectionPool.getInstance().release(conn);                       // release the connection
        }catch (SQLException e) {
            throw new DataException("Error saving record for Transaction ID = " + transIn.getTransID(), e);
        }catch (ConnectionPoolException x){
            throw new ConnectionPoolException("Could not establish connection to database.", x);
        }
        
        // release the connection back to the pool
        return;
    }
    
    /** This is a package method that is called by the public save (above) or
     *  by another DAO.  Either way we already have a connection to the database
     *  to use.  The user (controller) code never calls this one directly, since
     *  it can't know about Connection objects or SQLExceptions.
     *
     *  By having other DAOs call this save method (rather than update or
     *  insert below, each DAO in a chained save (like the TransactionDAO calling
     *  MembershipDAO calling InterestDAO save chain) can independently decide
     *  whether to udpate or insert the BO it's saving.
     * 
     * @param trans contains the TransactionBO to be saved
     * @param conn contains the Connection that has been established to the DB
     *
     * @throws edu.byu.isys413.group1E.DataException catches any errors with incoming data
     * @throws java.sql.SQLException catches any errors with SQL execution
     * @throws edu.byu.isys413.group1E.ConnectionPoolException catches any errors when connecting to the DB.
     */
    synchronized void save(TransactionBO trans, Connection conn) throws SQLException, DataException, ConnectionPoolException {
        // call save(bo, conn) on any subobjects (like TransactionDAO to MembershipDAO)
        if(trans.getPmt() != null && trans.getPmt().isIsDirty() == true){
            PaymentDAO.getInstance().save(trans.getPmt(), conn);
        }
        
        // run update or insert
        if (trans.isIsDirty()) {
            if (trans.isAlreadyInDB()) {
                update(trans, conn);
            }else{
                insert(trans, conn);
            }
            // set the dirty flag to false now that we've saved it
            trans.setIsDirty(false);
        }
        
        
        // touch the cache for the object
        Cache c = Cache.getInstance();
        c.put(trans.getTransID(), trans);
    }
    
    /** This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL udpate statement and make it more readable.  But
     * logically, it's really just a part of save.
     * 
     * @param trans contains the transactionBO to be updated to the DB
     * @param conn contains the connection already established with the DB
     *
     * @throws edu.byu.isys413.group1E.DataException catches any errors with incoming data
     * @throws java.sql.SQLException catches any errors with SQL execution
     * @throws edu.byu.isys413.group1E.ConnectionPoolException catches any errors when connecting to the DB.
     */
    private synchronized void update(TransactionBO trans, Connection conn) throws SQLException,
            DataException, ConnectionPoolException {
        //update the DB
        PreparedStatement pstmt = conn.prepareStatement("UPDATE Transaction_tbl SET TxDate = ?, TaxRate = ?, TotalTax = ?," +
                "SubTotal = ?, Total = ?, DiscountTaken = ?, Status = ?, PaymentID = ?, StoreID = ? , EmployeeID = ? , " +
                "CustomerID = ? WHERE TransactionID = ?");
        pstmt.setDate(1, java.sql.Date.valueOf(trans.getTransDate()));
        pstmt.setDouble(2, trans.getTransTaxRate());
        pstmt.setDouble(3, trans.getTransTax());
        pstmt.setDouble(4, trans.getTransSubTotal());
        pstmt.setDouble(5, trans.getTransTotal());
        pstmt.setDouble(6, trans.getTotalDiscountTaken());
        pstmt.setString(7, trans.getTransStatus());
        //save the payment data if a payment was made
        if(trans.getPmt() != null){
            pstmt.setString(8, trans.getPmt().getPaymentID());
        }else{
            pstmt.setNull(8, Types.VARCHAR);
        }
        pstmt.setString(9, trans.getStoreID());
        pstmt.setString(10, trans.getEmployeeID());
        //set the customerID if not anonymous
        if(trans.getCustomerID() != null){
            pstmt.setString(11, trans.getCustomerID());
        }else{
            pstmt.setNull(11, Types.VARCHAR);
        }
        pstmt.setString(12, trans.getTransID());
        pstmt.executeUpdate();
        
        //if a payment was made, save the payment
        if(trans.getPmt() != null){
            PaymentDAO.getInstance().save(trans.getPmt(),conn);
        }
        
        //save the transaction Lines
        if(trans.getTxLineList().isEmpty() == false){
            for(int x=0; x<trans.getTxLineList().size(); x++){
                TransLineDAO.getInstance().save(trans.getTxLineList().get(x), conn);
            }
        }
        
        pstmt.close();
    }
    
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL insert statement and make it more readable.  But
     * logically, it's really just a part of save.
     * @param trans contains the transactionBO to be updated to the DB
     * @param conn contains the connection already established with the DB
     * @throws edu.byu.isys413.group1E.DataException catches any errors with incoming data
     * @throws java.sql.SQLException catches any errors with SQL execution
     * @throws edu.byu.isys413.group1E.ConnectionPoolException catches any errors when connecting to the DB.
     */
    private synchronized void insert(TransactionBO trans, Connection conn) throws SQLException,
            DataException, ConnectionPoolException {
        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO Transaction_tbl (TransactionID, TxDate, TaxRate, TotalTax, " +
                "SubTotal, Total, DiscountTaken, Status, PaymentID, StoreID, EmployeeID, CustomerID) " +
                "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        pstmt.setString(1, trans.getTransID());
        pstmt.setDate(2, java.sql.Date.valueOf(trans.getTransDate()));
        pstmt.setDouble(3, trans.getTransTaxRate());
        pstmt.setDouble(4, trans.getTransTax());
        pstmt.setDouble(5, trans.getTransSubTotal());
        pstmt.setDouble(6, trans.getTransTotal());
        pstmt.setDouble(7, trans.getTotalDiscountTaken());
        pstmt.setString(8, trans.getTransStatus());
        //save the payment data if a payment was made
        if(trans.getPmt() != null){
            pstmt.setString(9, trans.getPmt().getPaymentID());
        }else{
            pstmt.setNull(9, Types.VARCHAR);
        }
        pstmt.setString(10, trans.getStoreID());
        pstmt.setString(11, trans.getEmployeeID());
        //set the customerID if not anonymous
        if(trans.getCustomerID() != null){
            pstmt.setString(12, trans.getCustomerID());
        }else{
            pstmt.setNull(12, Types.VARCHAR);
        }
        pstmt.executeUpdate();
        
        //if a payment was made, save the payment
        if(trans.getPmt() != null){
            PaymentDAO.getInstance().save(trans.getPmt(), conn);
        }
        
        //save the transaction Lines
        if(trans.getTxLineList().isEmpty() == false){
            for(int x=0; x<trans.getTxLineList().size(); x++){
                TransLineDAO.getInstance().save(trans.getTxLineList().get(x), conn);
            }
        }
        // tell the object that it's now in the db (so we call update next time not insert)
        trans.setAlreadyInDB(true);
    }
    
    
    ///////////////////////////////////////////////////////////////////////////////
    /////////   DELETE
    
    /** I did not include the DELETE functionality intentionally due to instructions received, as well as
     * to protect the integrity of the data.
     */
}
